package com.tuanzi.loan.business.repository.statistics.impl;

import com.tuanzi.loan.business.repository.statistics.NewUnionLoginStatRepository;
import com.tuanzi.loan.core.repository.JOOQBaseRepositoryPlus;
import com.tuanzi.loan.vo.request.statistics.union.NewUnionLoginStatRequest;
import com.tuanzi.loan.vo.response.statistics.union.NewUnionLoginStatResponse;
import org.apache.commons.lang3.StringUtils;
import org.jooq.*;
import org.jooq.impl.DSL;
import org.jooq.impl.TableImpl;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import static com.tuanzi.loan.generated.jooq.loan.tables.CommProductPrdid.COMM_PRODUCT_PRDID;
import static com.tuanzi.loan.generated.jooq.loan.tables.LoanUnionLoginRecord.LOAN_UNION_LOGIN_RECORD;

/**
 * User: liangZongXi
 * Create time: 15:01 2019/9/10
 * Description:
 */
@Repository
public class NewUnionLoginStatRepositoryImpl extends JOOQBaseRepositoryPlus implements NewUnionLoginStatRepository {

    @Override
    public Page<NewUnionLoginStatResponse> totalList(NewUnionLoginStatRequest newUnionLoginStatRequest, Pageable pageable) {
        Condition condition = DSL.trueCondition();
        List<GroupField> groupFieldList = new ArrayList<>();
        List<SelectField<?>> selectFieldList = new ArrayList<>();

        Date beginDate = newUnionLoginStatRequest.getBeginDate();
        if (null != beginDate) {
            condition = condition.and(LOAN_UNION_LOGIN_RECORD.CREATE_DATE.ge(new java.sql.Date(beginDate.getTime())));
        }
        Date endDate = newUnionLoginStatRequest.getEndDate();
        if (null != endDate) {
            condition = condition.and(LOAN_UNION_LOGIN_RECORD.CREATE_DATE.le(new java.sql.Date(endDate.getTime())));
        }
        groupFieldList.add(LOAN_UNION_LOGIN_RECORD.CREATE_DATE);
        selectFieldList.add(LOAN_UNION_LOGIN_RECORD.CREATE_DATE);

        Long activityId = newUnionLoginStatRequest.getActivityId();
        if (null != activityId) {
            condition = condition.and(LOAN_UNION_LOGIN_RECORD.PRODUCT_ID.eq(activityId));
        }else {
            condition = condition.and(LOAN_UNION_LOGIN_RECORD.PRODUCT_ID.isNotNull());
        }
        groupFieldList.add(LOAN_UNION_LOGIN_RECORD.PRODUCT_ID);
        selectFieldList.add(LOAN_UNION_LOGIN_RECORD.PRODUCT_ID);

        Integer channel = newUnionLoginStatRequest.getChannel();
        if (null != channel) {
            groupFieldList.add(LOAN_UNION_LOGIN_RECORD.CHANNEL);
            selectFieldList.add(LOAN_UNION_LOGIN_RECORD.CHANNEL);
            condition = condition.and(LOAN_UNION_LOGIN_RECORD.CHANNEL.eq(channel));
        }else{
            selectFieldList.add(DSL.field("'全部'").as("CHANNEL"));
        }

        String productId = newUnionLoginStatRequest.getProductId();
        if (StringUtils.isNotBlank(productId)) {
            groupFieldList.add(LOAN_UNION_LOGIN_RECORD.PRODUCT);
            selectFieldList.add(LOAN_UNION_LOGIN_RECORD.PRODUCT);
            condition = condition.and(LOAN_UNION_LOGIN_RECORD.PRODUCT.eq(productId));
        }else {
            selectFieldList.add(DSL.field("'全部'").as("PRODUCT"));
            condition = condition.and(LOAN_UNION_LOGIN_RECORD.PRODUCT.isNotNull());
        }

        String platform = newUnionLoginStatRequest.getPlatform();
        if (StringUtils.isNotBlank(platform)) {
            groupFieldList.add(LOAN_UNION_LOGIN_RECORD.PLATFORM);
            selectFieldList.add(LOAN_UNION_LOGIN_RECORD.PLATFORM);
            condition = condition.and(LOAN_UNION_LOGIN_RECORD.PLATFORM.eq(platform));
        }else{
            selectFieldList.add(DSL.field("'全部'").as("PLATFORM"));
        }

        groupFieldList.add(LOAN_UNION_LOGIN_RECORD.UNION_LOGIN_TYPE);
        selectFieldList.add(LOAN_UNION_LOGIN_RECORD.UNION_LOGIN_TYPE);

        Table<Record> reqNumSQL = dslContext.select(selectFieldList)
                .select(DSL.countDistinct(LOAN_UNION_LOGIN_RECORD.PHONE.cast(Integer.class)).as("REQ_NUM"))
                .from(LOAN_UNION_LOGIN_RECORD).where(condition).groupBy(groupFieldList).asTable();

        SelectQuery<Record> req_num = dslContext.select(selectFieldList)
                .select(DSL.countDistinct(LOAN_UNION_LOGIN_RECORD.PHONE.cast(Integer.class)).as("REQ_NUM"))
                .from(LOAN_UNION_LOGIN_RECORD).where(condition).groupBy(groupFieldList).getQuery();


        Table<Record> newNumSQL = dslContext.select(selectFieldList)
                .select(DSL.countDistinct(LOAN_UNION_LOGIN_RECORD.PHONE.cast(Integer.class)).as("NEW_NUM"))
                .from(LOAN_UNION_LOGIN_RECORD).where(LOAN_UNION_LOGIN_RECORD.IS_NEW_USER.eq((byte) 1)).and(condition)
                .groupBy(groupFieldList).asTable();

        Table<Record> oldNumSQL = dslContext.select(selectFieldList)
                .select(DSL.countDistinct(LOAN_UNION_LOGIN_RECORD.PHONE.cast(Integer.class)).as("OLD_NUM"))
                .from(LOAN_UNION_LOGIN_RECORD).where(LOAN_UNION_LOGIN_RECORD.IS_NEW_USER.eq((byte) 0)).and(condition)
                .groupBy(groupFieldList).asTable();


        SelectQuery<? extends Record10<java.sql.Date, String, Integer, String, Integer, Long, String, ?, ?, ?>> query = dslContext.select(reqNumSQL.field(LOAN_UNION_LOGIN_RECORD.CREATE_DATE),
                reqNumSQL.field(LOAN_UNION_LOGIN_RECORD.PRODUCT),
                reqNumSQL.field(LOAN_UNION_LOGIN_RECORD.CHANNEL),
                reqNumSQL.field(LOAN_UNION_LOGIN_RECORD.PLATFORM),
                reqNumSQL.field(LOAN_UNION_LOGIN_RECORD.UNION_LOGIN_TYPE),
                reqNumSQL.field(LOAN_UNION_LOGIN_RECORD.PRODUCT_ID),
                COMM_PRODUCT_PRDID.PARENT_ID,
                reqNumSQL.field("REQ_NUM", Integer.class),
                newNumSQL.field("NEW_NUM", Integer.class),
                oldNumSQL.field("OLD_NUM", Integer.class))
                .from(
                        reqNumSQL.leftJoin(newNumSQL)
                                .on(
                                        reqNumSQL.field(LOAN_UNION_LOGIN_RECORD.CREATE_DATE).eq(newNumSQL.field(LOAN_UNION_LOGIN_RECORD.CREATE_DATE)),
                                        reqNumSQL.field(LOAN_UNION_LOGIN_RECORD.PRODUCT).eq(newNumSQL.field(LOAN_UNION_LOGIN_RECORD.PRODUCT)),
                                        reqNumSQL.field(LOAN_UNION_LOGIN_RECORD.CHANNEL).eq(newNumSQL.field(LOAN_UNION_LOGIN_RECORD.CHANNEL)),
                                        reqNumSQL.field(LOAN_UNION_LOGIN_RECORD.PLATFORM).eq(newNumSQL.field(LOAN_UNION_LOGIN_RECORD.PLATFORM)),
                                        reqNumSQL.field(LOAN_UNION_LOGIN_RECORD.UNION_LOGIN_TYPE).eq(newNumSQL.field(LOAN_UNION_LOGIN_RECORD.UNION_LOGIN_TYPE)),
                                        reqNumSQL.field(LOAN_UNION_LOGIN_RECORD.PRODUCT_ID).eq(newNumSQL.field(LOAN_UNION_LOGIN_RECORD.PRODUCT_ID))
                                )
                                .leftJoin(oldNumSQL)
                                .on(
                                        reqNumSQL.field(LOAN_UNION_LOGIN_RECORD.CREATE_DATE).eq(oldNumSQL.field(LOAN_UNION_LOGIN_RECORD.CREATE_DATE)),
                                        reqNumSQL.field(LOAN_UNION_LOGIN_RECORD.PRODUCT).eq(oldNumSQL.field(LOAN_UNION_LOGIN_RECORD.PRODUCT)),
                                        reqNumSQL.field(LOAN_UNION_LOGIN_RECORD.CHANNEL).eq(oldNumSQL.field(LOAN_UNION_LOGIN_RECORD.CHANNEL)),
                                        reqNumSQL.field(LOAN_UNION_LOGIN_RECORD.PLATFORM).eq(oldNumSQL.field(LOAN_UNION_LOGIN_RECORD.PLATFORM)),
                                        reqNumSQL.field(LOAN_UNION_LOGIN_RECORD.UNION_LOGIN_TYPE).eq(oldNumSQL.field(LOAN_UNION_LOGIN_RECORD.UNION_LOGIN_TYPE)),
                                        reqNumSQL.field(LOAN_UNION_LOGIN_RECORD.PRODUCT_ID).eq(oldNumSQL.field(LOAN_UNION_LOGIN_RECORD.PRODUCT_ID))
                                )
                                .leftJoin(COMM_PRODUCT_PRDID)
                                .on(reqNumSQL.field(LOAN_UNION_LOGIN_RECORD.PRODUCT).eq(COMM_PRODUCT_PRDID.PRDID.cast(String.class)))

                ).orderBy(reqNumSQL.field(LOAN_UNION_LOGIN_RECORD.CREATE_DATE).desc()).getQuery();

        return search(query, pageable, record -> {
            NewUnionLoginStatResponse response = new NewUnionLoginStatResponse();
            response.setCreateDate(record.get(LOAN_UNION_LOGIN_RECORD.CREATE_DATE));
            response.setParentId(record.get(COMM_PRODUCT_PRDID.PARENT_ID));
            response.setProductName(record.get(LOAN_UNION_LOGIN_RECORD.PRODUCT));
            response.setPlatform(record.get(LOAN_UNION_LOGIN_RECORD.PLATFORM));
            Object objChannel = record.get("CHANNEL");
            response.setChannel(null == objChannel ? null : objChannel.toString());
            response.setUnionLoginType(record.get(LOAN_UNION_LOGIN_RECORD.UNION_LOGIN_TYPE));
            response.setActivity(record.get(LOAN_UNION_LOGIN_RECORD.PRODUCT_ID));
            Object reqNum = record.get("REQ_NUM");
            response.setReqNum(reqNum == null ? 0 : (Integer) reqNum);
            Object newNum = record.get("NEW_NUM");
            response.setNewNum(newNum == null ? 0 : (Integer) newNum);
            Object oldNum = record.get("OLD_NUM");
            response.setOldNum(null == oldNum ? 0 : null);
            return response;
        });
    }


    @Override
    protected TableImpl getTable() {
        return LOAN_UNION_LOGIN_RECORD;
    }
}
